package com.app.micaihu.db;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.TextUtils;
import android.util.ArrayMap;

import com.app.micaihu.bean.config.ChannelItem;
import com.app.micaihu.bean.news.NewsEntity;
import com.app.utils.util.GsonUtils;
import com.blankj.utilcode.util.LogUtils;
import com.blankj.utilcode.util.StringUtils;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 操作新闻数据库的工具类
 */
public class NewsDBUtil {

    private int saveBrowseHistorySize = 100;// 浏览历史保存数量
    private volatile static NewsDBUtil mInstance;
    private SQLiteOpenHelper mSQLHelp;
    private SQLiteDatabase db;
//	private SQLiteDatabase mSQLiteDatabase;

    private NewsDBUtil() {
        mSQLHelp = new NewsDBSqlHelper();
    }

    /**
     * 初始化数据库操作DBUtil类
     */
    public static NewsDBUtil getInstance() {
        if (mInstance == null) {
            synchronized (NewsDBUtil.class) {
                if (mInstance == null) {
                    mInstance = new NewsDBUtil();
                }
            }
        }
        return mInstance;
    }

    public SQLiteOpenHelper getSQLHelp() {
        if (mSQLHelp == null) {
            mSQLHelp = new NewsDBSqlHelper();
        }
        return mSQLHelp;
    }

    /**
     * 返回新闻栏目表名称
     *
     * @return
     */
    public String getNewsChannelTable() {
        return NewsDBSqlHelper.NEWS_CHANNEL;
    }

    /**
     * 返回收藏列表的表名
     */
    public String getCollectionTable() {
        return NewsDBSqlHelper.NEWS_SAVE;
    }


    /*********************共用的操作方法 start **************/

    /**
     * 关闭数据库
     */
    public void close() {

        SQLiteDatabase sQLiteDatabase = null;
        if (sQLiteDatabase != null) {
            sQLiteDatabase.close();
        }
        sQLiteDatabase = null;
        if (mSQLHelp != null) {
            mSQLHelp.close();
        }
        mSQLHelp = null;
        mInstance = null;
    }

    /**
     * 实现数据库的 增,删,改 操作
     *
     * @param sql sql语句
     * @return
     */
    public synchronized boolean changeData(String sql) {
        boolean flag = false;
        SQLiteDatabase sQLiteDatabase = null;
        try {
            if (mSQLHelp == null) {
                mSQLHelp = new NewsDBSqlHelper();
            }
            sQLiteDatabase = mSQLHelp.getReadableDatabase();
            sQLiteDatabase.beginTransaction();
            sQLiteDatabase.execSQL(sql);
            sQLiteDatabase.setTransactionSuccessful();
            flag = true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (sQLiteDatabase != null && sQLiteDatabase.inTransaction()) {
                try {
                    sQLiteDatabase.endTransaction();
                    sQLiteDatabase.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return flag;
    }

    /**
     * 清空表数据
     */
    public void clearTable(String table) {
        if (mSQLHelp == null) {
            mSQLHelp = new NewsDBSqlHelper();
        }
        SQLiteDatabase db = null;
        try {
            String sql = "DELETE FROM " + table + ";";
            db = mSQLHelp.getWritableDatabase();
            db.execSQL(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (db != null)
                db.close();
        }
    }

    /**
     * 查询表数据
     *
     * @param selection
     * @param selectionArgs
     * @param table
     * @return
     */
    public List<Map<String, String>> listCache(String selection, String[] selectionArgs, String orderByArg, String table, String limit) {
        List<Map<String, String>> list = new ArrayList<Map<String, String>>();
        SQLiteDatabase database = null;
        Cursor cursor = null;
        try {
            database = mSQLHelp.getReadableDatabase();
            cursor = database.query(false, table, null, selection, selectionArgs, null, null, orderByArg, limit);
            int cols_len = cursor.getColumnCount();

            while (cursor.moveToNext()) {
                Map<String, String> map = new HashMap<String, String>();
                for (int i = 0; i < cols_len; i++) {

                    String cols_name = cursor.getColumnName(i);
                    String cols_values = cursor.getString(cursor
                            .getColumnIndex(cols_name));
                    if (cols_values == null) {
                        cols_values = "";
                    }
                    map.put(cols_name, cols_values);
                }
                list.add(map);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (cursor != null) {
                    cursor.close();
                }
                if (database != null) {
                    database.close();
                }
            } catch (Exception e) {

            }
        }
        return list;
    }


    /*********************共用的操作方法 end **************/

    /*************** 独特的操作方法 start *******************/
    /**
     * 增加栏目
     */
    public boolean addCacheChannel(ChannelItem item, String table) {
        // TODO Auto-generated method stub
        boolean flag = false;
        SQLiteDatabase database = null;
        long id = -1;
        try {
            database = mSQLHelp.getWritableDatabase();
            ContentValues values = new ContentValues();
            values.put("name", item.getName());
            values.put("id", item.getId());
            values.put("orderId", item.getOrderId());
            values.put("selected", item.getSelected());
            values.put("type", item.getTypeid());
            id = database.insert(table, null, values);
            flag = (id != -1);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (database != null) {
                database.close();
            }
        }
        return flag;
    }

    /**
     * 增加收藏新闻
     */
    public boolean addSaveNews(NewsEntity item, String table, String saveTime) {
        if (item == null) {
            return false;
        }
        boolean flag = false;
        SQLiteDatabase database = null;
        long id = -1;
        try {
            database = mSQLHelp.getWritableDatabase();
            //增加事物
            database.beginTransaction();

            ContentValues values = new ContentValues();
            values.put("_id", item.getArticleId());
            values.put("articleAttr", item.getArticleAttr());
            values.put("articleMark", item.getArticleMark());
            values.put("articleTime", item.getArticleTime());
            values.put("articleTitle", item.getArticleTitle());
            values.put("articleType", item.getArticleType());
            values.put("commentNum", item.getCommentNum());
            values.put("uiType", item.getUiType());
            values.put("praiseNum", item.getPicNum());
            values.put("categoryId", item.getCategoryId());
            //收藏的时间
            values.put("savetime", saveTime);
            if (item.getArticleThumb() != null && item.getArticleThumb().length > 0) {
                String pic = "";
                for (int i = 0; i < item.getArticleThumb().length; i++) {
                    pic += item.getArticleThumb()[i] + "~";
                }
                values.put("articleThumb", pic);
            }

            id = database.insert(table, null, values);
            //增加事物
            database.setTransactionSuccessful();

            flag = (id != -1);
        } catch (Exception e) {
            // TODO: handle exception
        } finally {
            if (database != null && database.inTransaction()) {
                //增加事物
                try {
                    database.endTransaction();
                    database.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return flag;
    }


    /**
     * 判断表中是否有符合sql的数据
     *
     * @param sql
     * @return
     */
    public boolean hasDataBySql(String sql) {
        boolean flag = false;
        Cursor cursor = null;
        SQLiteDatabase sqLiteDatabase = null;
        try {
            sqLiteDatabase = mSQLHelp.getReadableDatabase();
            cursor = sqLiteDatabase.rawQuery(sql, null);
            while (cursor.moveToNext()) {
                flag = true;
                break;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (cursor != null) {
                    cursor.close();
                }
                if (sqLiteDatabase != null) {
                    sqLiteDatabase.close();
                }

            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return flag;
    }

    /**
     * 执行sql 返回 数据id集合
     *
     * @param sql
     * @return
     */
    public List<String> getListID(String sql) {
        Cursor cursor = null;
        SQLiteDatabase sqLiteDatabase = null;
        List<String> list = new ArrayList<String>();
        try {


            sqLiteDatabase = mSQLHelp.getReadableDatabase();
            cursor = sqLiteDatabase.rawQuery(sql, null);
            if (cursor != null) {
                while (cursor.moveToNext()) {
                    list.add(cursor.getString(cursor.getColumnIndex("_id")));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                cursor.close();
                sqLiteDatabase.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        return list;
    }


    /*************** 独特的操作方法 end *******************/


    /**
     * 清理新闻数据库
     * 所有表数据大于100条则开始清理
     */
    public void dbClear() {
        Cursor cursor = null;
        SQLiteDatabase sqLiteDatabase = null;
        try {
            sqLiteDatabase = mSQLHelp.getReadableDatabase();
            int count = 0;
            //清除评论点赞表
            String sql = "select count(*) from "
                    + NewsDBSqlHelper.COMMENT_ZAN;
            cursor = sqLiteDatabase.rawQuery(sql, null);
            if (cursor != null) {
                count = cursor.getCount();
                if (count > 500) {
                    sql = "delete from " + NewsDBSqlHelper.COMMENT_ZAN;
                    sqLiteDatabase.execSQL(sql);
                }
            }
            cursor.close();
            //清除新闻稿子的表
            sql = "select count(*) from " + NewsDBSqlHelper.NEWS_SEE;
            cursor = sqLiteDatabase.rawQuery(sql, null);
            if (cursor != null) {
                count = cursor.getCount();
                if (count > 300) {
                    sql = "delete from " + NewsDBSqlHelper.NEWS_SEE;
                    sqLiteDatabase.execSQL(sql);
                }
            }
            cursor.close();
            //清除打赏表
            sql = "select count(*) from " + NewsDBSqlHelper.COMMENT_REPORT;
            cursor = sqLiteDatabase.rawQuery(sql, null);
            if (cursor != null) {
                count = cursor.getCount();
                if (count > 500) {
                    sql = "delete from " + NewsDBSqlHelper.COMMENT_REPORT;
                    sqLiteDatabase.execSQL(sql);
                }
            }
            cursor.close();
            cursor = null;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cursor != null && !cursor.isClosed()) {
                try {
                    cursor.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (sqLiteDatabase != null) {
                try {
                    sqLiteDatabase.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }


    /**
     * 分页查询本地的
     *
     * @param offset 从
     * @param limit  到
     * @return 返回的json字符串，至少有一个item的数据
     */
    public String getLocalRecomendList(int offset, int limit) {
        Object cacheList = listCache(null, null, "exactTime desc", NewsDBSqlHelper.EDITOR_RECOMMEND, offset + "," + limit);//offset+","+MaxResult
        StringBuilder stringBuilder = new StringBuilder();
        if (cacheList != null && !((List) cacheList).isEmpty()) {
            List<Map<String, String>> maplist = (List) cacheList;
            int count = maplist.size();
            //通过字符串
            if (count <= 1) {
                stringBuilder.append("[" + maplist.get(0).get("newsList") + "]");
            } else {
                for (int i = count - 1; i >= 0; i--) {
                    if (i == 0) {
                        stringBuilder.append(maplist.get(i).get("newsList") + "]");
                    } else if (i == count - 1) {
                        stringBuilder.append("[" + maplist.get(i).get("newsList") + ",");
                    } else {
                        stringBuilder.append(maplist.get(i).get("newsList") + ",");
                    }
                }
            }


        }
        return stringBuilder.toString();

    }


    /**
     * 添加推荐数据
     *
     * @param key
     * @param value
     * @param isInsert
     * @return
     */
    public boolean insertRecommend(String key, String value, boolean isInsert) {

        // TODO Auto-generated method stub
        boolean flag = false;
        SQLiteDatabase database = null;
        long id = -1;
        if (mSQLHelp == null) {
            mSQLHelp = new NewsDBSqlHelper();
        }
        try {
            database = mSQLHelp.getWritableDatabase();
            ContentValues values = new ContentValues();

            if (isInsert) {
                values.put("exactTime", key);
                values.put("newsList", value);
                id = database.insert(NewsDBSqlHelper.EDITOR_RECOMMEND, null, values);
                //id != -1 表示成功
                flag = (id != -1);
            } else {
                values.put("newsList", value);
                String[] args = {key};
                id = database.update(NewsDBSqlHelper.EDITOR_RECOMMEND, values, "exactTime=?", args);
                //id = 1 才是更改
                flag = (id == 1);
            }


        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            if (database != null) {
                database.close();
            }
        }
        return flag;
    }

    /**
     * 修改新闻内页 赞 表的方法
     *
     * @param insertOrdel true ：插入；false：删除
     * @param newsId      修改的新闻id
     * @param iszan       是否为点赞操作
     */
    public void changeNewsZAN(boolean insertOrdel, String newsId, boolean iszan) {
        if (!TextUtils.isEmpty(newsId)) {
            String sql = "";
            if (insertOrdel) {
                String type;
                if (iszan) {
                    type = "0";
                } else {
                    type = "1";
                }
                sql = "insert into " + NewsDBSqlHelper.NEWS_ZAN + " values ('" + newsId + "','" + type + "');";
            } else {
                sql = "delete from " + NewsDBSqlHelper.NEWS_ZAN + " where _id = '" + newsId + "';";
            }
            changeData(sql);
        }
    }

    /**
     * 查询是否赞过
     *
     * @param newsId
     * @return 返回 0点赞过 1踩过  null无记录
     */
    public String queryZanId(String newsId) {
        String sql = "select type from " + NewsDBSqlHelper.NEWS_ZAN + " where _id = '" + newsId + "';";
        Cursor cursor = null;
        SQLiteDatabase sqLiteDatabase = null;
        String type = null;
        try {
            sqLiteDatabase = mSQLHelp.getReadableDatabase();
            cursor = sqLiteDatabase.rawQuery(sql, null);
            if (cursor.moveToNext()) {
                type = cursor.getString(0);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (cursor != null) {
                    cursor.close();
                }
                if (sqLiteDatabase != null) {
                    sqLiteDatabase.close();
                }

            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return type;
    }

    private String tableUserId = "userId";
    private String tableHistory = "history";

    /**
     * 添加数据到浏览历史表中
     */
    public void addHistoryData(String id, String history) {
        List<NewsEntity> historyList = getHistoryData();
        int historyListSize = historyList.size();
        if (historyListSize > 0) {
            for (NewsEntity newsEntity : historyList) {
                if (newsEntity == null) return;
                if (StringUtils.isEmpty(newsEntity.getArticleId()) || TextUtils.equals(id, newsEntity.getArticleId())) {
                    return;
                }
            }
        }
        SQLiteDatabase database = mSQLHelp.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(tableHistory, history);
        long index = database.insert(NewsDBSqlHelper.BROWSE_HISTORY, null, values);//返回新插入的行号，如果插入失败返回-1
        if (index > 0) {
            String deleteId = "";
            if (historyListSize > 0) {
                deleteId = historyList.get(historyListSize - 1).getTable_id();
            }
            if (historyListSize >= saveBrowseHistorySize) {
                deleteHistoryDate(deleteId);
            }
        }
    }

    public List<NewsEntity> getHistoryData() {
        List<NewsEntity> newsEntityList = new ArrayList<>();
        db = mSQLHelp.getReadableDatabase();
        String sql = "select * from " + NewsDBSqlHelper.BROWSE_HISTORY + " order by id desc";
        @SuppressLint("Recycle") Cursor cursor = db.rawQuery(sql, null);
        if (cursor != null) {
            while (cursor.moveToNext()) {//如果光标可以移动到下一位,代表就是查询到了数据
                String table_id = cursor.getString(cursor.getColumnIndex("id"));
                String table_history = cursor.getString(cursor.getColumnIndex(tableHistory));
                NewsEntity newsEntity = GsonUtils.getInstance().toObject(table_history, NewsEntity.class);
                newsEntity.setTable_id(table_id);
                newsEntityList.add(newsEntity);
            }
        }
        return newsEntityList;
    }

    /**
     * 添加数据到浏览历史表中
     */
    public void addGameState(String gameId) {
        ArrayMap<String, String> gameDownload = getGameDownload();
        int gameDownloadListSize = gameDownload.size();
        if (gameDownloadListSize > 0) {
            if (gameDownload.containsKey(gameId)) return;
        }
        SQLiteDatabase database = mSQLHelp.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("gameId", gameId);
        long index = database.insert(NewsDBSqlHelper.GAME_STATE, null, values);//返回新插入的行号，如果插入失败返回-1
        LogUtils.e("addGameState", "addGameState->" + index);
        if (index > 0) {
            LogUtils.e("addGameState", "插入成功");
        }
    }

    @SuppressLint("Recycle")
    public ArrayMap<String, String> getGameDownload() {
        ArrayMap<String, String> gameMap = new ArrayMap<>();
        db = mSQLHelp.getReadableDatabase();
        String sql = "select * from " + NewsDBSqlHelper.GAME_STATE;
        Cursor cursor = db.rawQuery(sql, null);
        if (cursor != null) {
            while (cursor.moveToNext()) {//如果光标可以移动到下一位,代表就是查询到了数据
                String table_id = cursor.getString(cursor.getColumnIndex("gameId"));
                gameMap.put(table_id, table_id);
            }
        }
        return gameMap;
    }


    // 删除的方法 第一行
    public void deleteHistoryDate(String id) {
        SQLiteDatabase database = mSQLHelp.getReadableDatabase();
        String sql = "delete from " + NewsDBSqlHelper.BROWSE_HISTORY + " where id= " + id;
        database.execSQL(sql);
        database.close();
    }

    // 删除的方法
    public void deleteHistoryDate() {
        SQLiteDatabase database = mSQLHelp.getReadableDatabase();
        String sql = "delete from " + NewsDBSqlHelper.BROWSE_HISTORY;
        database.execSQL(sql);
        database.close();
    }

}